import Database from 'better-sqlite3';
import {EntityTypeProcessor, QueryBuilderUtils, SqlBuilderUtils} from '../common';
import {
  ComparisonOperator,
  EntityClass,
  JoinCondition,
  LogicalDeleteConfig,
  LogicalDeleteInfo,
  OrderByOption,
  OrderCondition,
  TableInfo,
  WhereCondition
} from "../types";
import {DEFAULT_LOGICAL_DELETED_VALUE, DEFAULT_LOGICAL_NOT_DELETED_VALUE} from './DatabaseManager';

/**
 * 用于构建复杂查询的SQL查询构建器
 */
export class QueryBuilder<T = any> {
  private db: Database.Database;
  private entityClass: EntityClass;
  private tableName: string;
  private tableInfo: TableInfo<T>;
  private selectFields: (keyof T & string)[] | string[] = ['*'] as string[];
  private whereConditions: WhereCondition<T>[] = [];
  private orConditions: WhereCondition<T>[][] = [];
  private joinConditions: JoinCondition[] = [];
  private groupByFields: (keyof T & string)[] = [];
  private havingConditions: WhereCondition<T>[] = [];
  private orderByConditions: OrderCondition<T>[] = [];
  private limitValue?: number;
  private offsetValue?: number;
  private params: any[] = [];
  private ignoreLogicalDelete: boolean = false;
  private logicalDeleteField: string | null = null;
  private logicalDeleteConfig: LogicalDeleteConfig | null = null;
  private logicalDeleteInfo: LogicalDeleteInfo | null = null;

  /**
   * 创建一个新的QueryBuilder实例
   * @param db 数据库实例
   * @param entity 实体类
   */
  constructor(db: Database.Database, entity: EntityClass) {
    this.db = db;
    this.entityClass = entity;
    this.tableInfo = entity.getTableInfo<T>();
    this.tableName = this.tableInfo.name;

    // 初始化逻辑删除配置
    this.initLogicalDeleteConfig();
  }

  /**
   * 初始化逻辑删除配置
   */
  private initLogicalDeleteConfig(): void {
    // 检查数据库是否有全局逻辑删除配置
    if (this.tableInfo.db.logicalDeleteConfig) {
      this.logicalDeleteConfig = this.tableInfo.db.logicalDeleteConfig;
      this.logicalDeleteField = this.logicalDeleteConfig.deletedField || 'deleted';
    }

    // 检查实体是否有使用@LogicalDelete装饰器的字段
    for (const [fieldName, column] of Object.entries<any>(this.tableInfo.columns)) {
      if (column.logicalDelete) {
        this.logicalDeleteField = fieldName;
        this.logicalDeleteInfo = column.logicalDelete;
        break;
      }
    }
  }

  /**
   * 获取逻辑删除字段的值
   * @param isDeleted 是否是已删除状态
   * @returns 对应状态的字段值
   */
  private getLogicalDeleteValue(isDeleted: boolean): any {
    // 使用DatabaseManager中定义的默认值
    const defaultDeletedValue = DEFAULT_LOGICAL_DELETED_VALUE;
    const defaultNotDeletedValue = DEFAULT_LOGICAL_NOT_DELETED_VALUE;

    if (this.logicalDeleteInfo) {
      // 优先使用字段级配置
      // 确保有值，即使字段上的LogicalDelete装饰器没有提供参数
      return isDeleted
        ? (this.logicalDeleteInfo.deletedValue !== undefined ? this.logicalDeleteInfo.deletedValue : defaultDeletedValue)
        : (this.logicalDeleteInfo.notDeletedValue !== undefined ? this.logicalDeleteInfo.notDeletedValue : defaultNotDeletedValue);
    } else if (this.logicalDeleteConfig) {
      // 其次使用全局配置
      return isDeleted
        ? (this.logicalDeleteConfig.deletedValue !== undefined ? this.logicalDeleteConfig.deletedValue : defaultDeletedValue)
        : (this.logicalDeleteConfig.notDeletedValue !== undefined ? this.logicalDeleteConfig.notDeletedValue : defaultNotDeletedValue);
    }
    // 默认返回数值
    return isDeleted ? defaultDeletedValue : defaultNotDeletedValue;
  }

  /**
   * 检查是否启用了逻辑删除
   * @returns 是否启用了逻辑删除
   */
  private isLogicalDeleteEnabled(): boolean {
    return !this.ignoreLogicalDelete && !!this.logicalDeleteField;
  }

  /**
   * 设置要选择的字段
   * @param fields 字段名
   * @returns 当前实例，用于链式调用
   */
  select(...fields: (keyof T & string)[]): QueryBuilder<T> {
    if (fields.length > 0) {
      this.selectFields = fields;
    }
    return this;
  }

  /**
   * 添加WHERE条件
   * @param field 字段名
   * @param operator 比较运算符
   * @param value 比较值
   * @returns 当前实例，用于链式调用
   */
  where(field: keyof T & string, operator: ComparisonOperator, value?: any): QueryBuilder<T> {
    // 获取是否启用严格类型检查
    const strictTypeCheck = this.tableInfo.db.isStrictTypeCheckEnabled();

    // 进行类型校验或转换（对于WHERE条件不需要严格检查）
    const processedValue = EntityTypeProcessor.processFieldValue(field, value, this.tableInfo, strictTypeCheck);

    this.whereConditions.push({field, operator, value: processedValue});

    if (processedValue !== undefined && !['IS NULL', 'IS NOT NULL'].includes(operator)) {
      if (operator === 'BETWEEN' && Array.isArray(processedValue)) {
        this.params.push(processedValue[0], processedValue[1]);
      } else if (operator === 'IN' || operator === 'NOT IN') {
        if (Array.isArray(processedValue)) {
          this.params.push(...processedValue);
        }
      } else {
        this.params.push(processedValue);
      }
    }

    return this;
  }

  /**
   * 添加AND条件
   * @param field 字段名
   * @param operator 比较运算符
   * @param value 比较值
   * @returns 当前实例，用于链式调用
   */
  and(field: keyof T & string, operator: ComparisonOperator, value?: any): QueryBuilder<T> {
    return this.where(field, operator, value);
  }

  /**
   * 添加OR条件
   * @param conditions 用于创建OR条件的回调函数
   * @returns 当前实例，用于链式调用
   */
  or(conditions: (qb: QueryBuilder<T>) => void): QueryBuilder<T> {
    const subQuery = new QueryBuilder<T>(this.db, this.entityClass);
    conditions(subQuery);

    if (subQuery.whereConditions.length > 0) {
      this.orConditions.push(subQuery.whereConditions);
      this.params.push(...subQuery.params);
    }

    return this;
  }

  /**
   * 添加带括号的分组AND条件
   * @param conditions 用于创建分组条件的回调函数
   * @returns 当前实例，用于链式调用
   */
  andWhere(conditions: (qb: QueryBuilder<T>) => void): QueryBuilder<T> {
    const subQuery = new QueryBuilder<T>(this.db, this.entityClass);
    conditions(subQuery);

    if (subQuery.whereConditions.length > 0) {
      this.whereConditions.push({
        field: `(${this.buildWhereConditionString(subQuery.whereConditions)})` as keyof T & string,
        operator: '=',
        value: undefined
      });

      this.params.push(...subQuery.params);
    }

    return this;
  }

  /**
   * 添加表连接
   * @param table 要连接的表
   * @param type 连接类型
   * @param on ON子句
   * @returns 当前实例，用于链式调用
   */
  join(table: string, type: 'INNER' | 'LEFT' | 'RIGHT' | 'FULL', on: string): QueryBuilder<T> {
    this.joinConditions.push({table, type, on});
    return this;
  }

  /**
   * 添加INNER JOIN
   * @param table 要连接的表
   * @param on ON子句
   * @returns 当前实例，用于链式调用
   */
  innerJoin(table: string, on: string): QueryBuilder<T> {
    return this.join(table, 'INNER', on);
  }

  /**
   * 添加LEFT JOIN
   * @param table 要连接的表
   * @param on ON子句
   * @returns 当前实例，用于链式调用
   */
  leftJoin(table: string, on: string): QueryBuilder<T> {
    return this.join(table, 'LEFT', on);
  }

  /**
   * 设置GROUP BY字段
   * @param fields 字段名
   * @returns 当前实例，用于链式调用
   */
  groupBy(...fields: (keyof T & string)[]): QueryBuilder<T> {
    this.groupByFields = fields;
    return this;
  }

  /**
   * 添加HAVING条件
   * @param field 字段名
   * @param operator 比较运算符
   * @param value 比较值
   * @returns 当前实例，用于链式调用
   */
  having(field: keyof T & string, operator: ComparisonOperator, value?: any): QueryBuilder<T> {
    // 获取是否启用严格类型检查
    const strictTypeCheck = this.tableInfo.db.isStrictTypeCheckEnabled();

    // 进行类型校验或转换（对于HAVING条件不需要严格检查）
    const processedValue = EntityTypeProcessor.processFieldValue(field, value, this.tableInfo, strictTypeCheck);

    this.havingConditions.push({field, operator, value: processedValue});

    if (processedValue !== undefined && !['IS NULL', 'IS NOT NULL'].includes(operator)) {
      this.params.push(processedValue);
    }

    return this;
  }

  /**
   * 设置ORDER BY子句
   * @param field 字段名
   * @param direction 排序方向
   * @returns 当前实例，用于链式调用
   */
  orderBy(field: keyof T & string, direction: 'ASC' | 'DESC' = 'ASC'): QueryBuilder<T> {
    this.orderByConditions.push({field, direction});
    return this;
  }

  /**
   * 使用对象设置多个ORDER BY条件
   * @param orderByObj ORDER BY条件对象
   * @returns 当前实例，用于链式调用
   */
  orderByMultiple(orderByObj: OrderByOption<T>): QueryBuilder<T> {
    const orderByString = QueryBuilderUtils.buildOrderByClause(orderByObj);
    if (orderByString) {
      const orders = orderByString.split(',').map(part => {
        const [field, direction] = part.trim().split(' ');
        return {
          field: field as keyof T & string,
          direction: direction as 'ASC' | 'DESC'
        };
      });

      this.orderByConditions.push(...orders);
    }
    return this;
  }

  /**
   * 设置LIMIT子句
   * @param limit 最大结果数
   * @returns 当前实例，用于链式调用
   */
  limit(limit: number): QueryBuilder<T> {
    this.limitValue = limit;
    return this;
  }

  /**
   * 设置OFFSET子句
   * @param offset 要跳过的结果数
   * @returns 当前实例，用于链式调用
   */
  offset(offset: number): QueryBuilder<T> {
    this.offsetValue = offset;
    return this;
  }

  /**
   * 在查询中忽略逻辑删除条件
   * @returns 当前实例，用于链式调用
   */
  withDeleted(): QueryBuilder<T> {
    this.ignoreLogicalDelete = true;
    return this;
  }

  /**
   * 只查询已删除的记录
   * @returns 当前实例，用于链式调用
   */
  onlyDeleted(): QueryBuilder<T> {
    if (this.logicalDeleteField) {
      this.where(this.logicalDeleteField as keyof T & string, '=', this.getLogicalDeleteValue(true));
    }
    return this;
  }

  /**
   * 添加逻辑删除条件（如果未指定）
   */
  private addLogicalDeleteCondition(): void {
    if (!this.isLogicalDeleteEnabled() || !this.logicalDeleteField) {
      return;
    }

    // 检查是否已经有针对逻辑删除字段的条件
    const hasLogicalDeleteCondition = this.whereConditions.some(condition =>
      condition.field === this.logicalDeleteField
    );

    // 检查OR条件中是否有针对逻辑删除字段的条件
    const hasLogicalDeleteInOrCondition = this.orConditions.some(orGroup =>
      orGroup.some(condition => condition.field === this.logicalDeleteField)
    );

    // 如果没有逻辑删除条件，添加未删除条件
    if (!hasLogicalDeleteCondition && !hasLogicalDeleteInOrCondition) {
      const notDeletedValue = this.getLogicalDeleteValue(false);
      // 获取是否启用严格类型检查
      const strictTypeCheck = this.tableInfo.db.isStrictTypeCheckEnabled();

      // 进行类型校验或转换
      const fieldName = this.logicalDeleteField as keyof T & string;
      const processedValue = EntityTypeProcessor.processFieldValue(
        fieldName,
        notDeletedValue,
        this.tableInfo,
        strictTypeCheck
      );

      this.whereConditions.push({
        field: fieldName,
        operator: '=',
        value: processedValue
      });

      this.params.push(processedValue);
    }
  }

  /**
   * 构建WHERE条件字符串
   * @param conditions WHERE条件
   * @returns WHERE条件字符串
   */
  private buildWhereConditionString(conditions: WhereCondition<T>[]): string {
    return conditions.map(condition => {
      if (['IS NULL', 'IS NOT NULL'].includes(condition.operator)) {
        return `${condition.field} ${condition.operator}`;
      } else if (condition.operator === 'BETWEEN' && Array.isArray(condition.value)) {
        return `${condition.field} BETWEEN ? AND ?`;
      } else if (['IN', 'NOT IN'].includes(condition.operator) && Array.isArray(condition.value)) {
        const placeholders = condition.value.map(() => '?').join(', ');
        return `${condition.field} ${condition.operator} (${placeholders})`;
      } else if (String(condition.field).startsWith('(') && String(condition.field).endsWith(')')) {
        // For subqueries, just use the field name (which contains the entire condition)
        return String(condition.field);
      } else {
        return `${condition.field} ${condition.operator} ?`;
      }
    }).join(' AND ');
  }

  /**
   * 构建完整的SQL查询
   * @returns SQL查询和参数
   */
  build(): { sql: string; params: any[] } {
    // 添加逻辑删除条件（如果启用）
    this.addLogicalDeleteCondition();

    const params: any[] = [...this.params];

    // 处理WHERE条件
    const whereClause = this.buildWhereClauseString();

    // 处理GROUP BY
    let groupByClause: string | undefined;
    if (this.groupByFields.length > 0) {
      groupByClause = this.groupByFields.join(', ');
    }

    // 处理HAVING条件
    let havingClause: string | undefined;
    if (this.havingConditions.length > 0) {
      const havingClauses = this.havingConditions.map(condition => {
        const {field, operator, value} = condition;

        if (['IS NULL', 'IS NOT NULL'].includes(operator)) {
          return `${field} ${operator}`;
        } else if (operator === 'BETWEEN' && Array.isArray(value)) {
          return `${field} BETWEEN ? AND ?`;
        } else if ((operator === 'IN' || operator === 'NOT IN') && Array.isArray(value)) {
          const placeholders = value.map(() => '?').join(', ');
          return `${field} ${operator} (${placeholders})`;
        } else {
          return `${field} ${operator} ?`;
        }
      });

      havingClause = havingClauses.join(' AND ');
    }

    // 处理ORDER BY
    let orderByClause: string | undefined;
    if (this.orderByConditions.length > 0) {
      const orderClauses = this.orderByConditions.map(
        condition => `${condition.field} ${condition.direction}`
      );
      orderByClause = orderClauses.join(', ');
    }

    // 处理LIMIT和OFFSET
    let limitClause: string | undefined;
    if (this.limitValue !== undefined) {
      limitClause = `LIMIT ?`;
      params.push(this.limitValue);

      if (this.offsetValue !== undefined) {
        limitClause += ` OFFSET ?`;
        params.push(this.offsetValue);
      }
    }

    // 构建JOIN子句
    let joinClause = '';
    if (this.joinConditions.length > 0) {
      for (const join of this.joinConditions) {
        joinClause += ` ${join.type} JOIN ${join.table} ON ${join.on}`;
      }
    }

    // 构建完整的SQL语句
    let sql = '';

    // 如果是COUNT查询
    if (this.selectFields.length === 1 && String(this.selectFields[0]).includes('COUNT(*)')) {
      sql = SqlBuilderUtils.buildCountSql(this.tableName, whereClause);

      // 手动添加JOIN、GROUP BY和HAVING（因为COUNT查询的SqlBuilderUtils不支持这些）
      if (joinClause) {
        // 在WHERE之前插入JOIN
        if (whereClause) {
          sql = sql.replace(` WHERE ${whereClause}`, `${joinClause} WHERE ${whereClause}`);
        } else {
          sql = sql.replace(' FROM', ` FROM${joinClause}`);
        }
      }

      if (groupByClause) {
        sql += ` GROUP BY ${groupByClause}`;
      }

      if (havingClause) {
        sql += ` HAVING ${havingClause}`;
      }
    } else {
      // 普通SELECT查询
      sql = SqlBuilderUtils.buildSelectSql(
        this.tableName,
        this.selectFields as string[],
        whereClause,
        orderByClause,
        limitClause
      );

      // 手动添加JOIN、GROUP BY和HAVING
      if (joinClause) {
        // 在WHERE之前插入JOIN
        if (whereClause) {
          sql = sql.replace(` WHERE ${whereClause}`, `${joinClause} WHERE ${whereClause}`);
        } else {
          sql = sql.replace(' FROM', ` FROM${joinClause}`);
        }
      }

      if (groupByClause) {
        sql += ` GROUP BY ${groupByClause}`;
      }

      if (havingClause) {
        sql += ` HAVING ${havingClause}`;
      }
    }

    return {sql, params};
  }

  /**
   * 构建完整的WHERE子句字符串
   * @returns WHERE子句字符串
   */
  private buildWhereClauseString(): string {
    const clauses: string[] = [];

    // 处理普通WHERE条件
    if (this.whereConditions.length > 0) {
      clauses.push(this.buildWhereConditionString(this.whereConditions));
    }

    // 处理OR条件组
    for (const orGroup of this.orConditions) {
      if (orGroup.length > 0) {
        clauses.push(`(${this.buildWhereConditionString(orGroup)})`);
      }
    }

    return clauses.join(' AND ');
  }

  /**
   * 执行查询并返回所有结果
   * @returns 查询结果
   */
  all<R = T>(): R[] {
    const {sql, params} = this.build();
    const stmt = this.db.prepare(sql);
    return stmt.all(...params) as R[];
  }

  /**
   * 执行查询并返回第一个结果
   * @returns 第一个结果或null，如果没有
   */
  first<R = T>(): R | null {
    const {sql, params} = this.build();
    const stmt = this.db.prepare(sql);
    return stmt.get(...params) as R || null;
  }

  /**
   * 计算匹配记录的数量
   * @returns 记录数量
   */
  count(): number {
    // Save the current select fields
    const originalSelectFields = [...this.selectFields];

    // Change select to COUNT(*)
    this.selectFields = ['COUNT(*) as count'] as any;

    const {sql, params} = this.build();
    const stmt = this.db.prepare(sql);
    const result = stmt.get(...params) as { count: number } | undefined;

    // Restore original select fields
    this.selectFields = originalSelectFields;

    return result?.count || 0;
  }
}
